-- =============================================
-- This stored procedure gets descendatns for nav item.
--
-- Modifications
-- 06/11/2003    E.Tatsui   Created
-- =============================================

CREATE PROCEDURE amsp_CMGetPublishableNavMenu
  @InNavMenuID numeric,
  @InContactID numeric
AS
BEGIN

  DECLARE
    @MaxSort numeric(28,18),
    @MinSort numeric(28,18)

  SELECT @MinSort = a.SortOrder,
         @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
            FROM Nav_Menu x
            WITH (NOLOCK)
           WHERE x.SortOrder > a.SortOrder
             AND x.CategoryDepth <= a.CategoryDepth)
    FROM Nav_Menu a
   WHERE a.NavMenuID = @InNavMenuID

  SELECT a.NavMenuID,
         a.Title,
         a.CategoryDepth,
         (SELECT count(*)
            FROM Content z WITH (NOLOCK)
           WHERE z.NavMenuID = a.NavMenuID
             AND (z.PublishDateTime IS NULL OR z.PublishDateTime < CURRENT_TIMESTAMP)
             AND (z.WorkflowStatusCode = 'A' OR z.WorkflowStatusCode = 'P')) AS ContentCount
    FROM Nav_Menu a WITH (NOLOCK), Content_Authority_Producer b WITH (NOLOCK)
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @InContactID
     AND (b.NavCreatorFlag = 'Y' OR b.NavEditorFlag = 'Y')
     AND a.SortOrder > @MinSort
     AND a.SortOrder < @MaxSort
   ORDER BY a.SortOrder

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetPublishableNavMenu] TO [IMIS]
GO